package com.xiaoyu.izf.service;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Dict;
import com.google.common.collect.Lists;
import com.xiaoyu.izf.enums.RateSCEnum;
import com.xiaoyu.izf.model.UserInfo;
import com.xiaoyu.izf.model.YqfMerchant;
import com.xiaoyu.izf.model.YqfStore;
import com.xiaoyu.izf.model.YqfStoreStatsDay;
import com.xiaoyu.izf.model.page.DataTableBean;
import com.xiaoyu.izf.model.page.ParamListBean;
import com.xiaoyu.izf.repository.YqfMerchantDao;
import com.xiaoyu.izf.repository.YqfStoreSatsDayDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class YqfStoreStatsService extends BaseService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private YqfStoreSatsDayDao yqfStoreSatsDayDao;
    @Autowired
    private YqfMerchantDao yqfMerchantDao;

    public List<YqfMerchant> merchants(UserInfo user) {
        if (user.getIsAdmin() == 1) {
            return yqfMerchantDao.findAll();
        } else {
            return yqfMerchantDao.findAllByUid(user.getId());
        }
    }

    /**
     * 判断一个商户是否在指定合伙人管辖区域内
     *
     * @param user
     * @param merchant
     * @return
     */
    public boolean innerRegion(UserInfo user, YqfMerchant merchant) {
        if (user.getVipType() == 21) {
            return user.getRegionCode().substring(0, 2).equals(merchant.getRegionCode().substring(0, 2));
        } else if (user.getVipType() == 23) {
            return user.getRegionCode().equals(merchant.getRegionCode());
        } else {
            return false;
        }
    }

    /**
     * @param amount
     * @param pushType    1、直推；2、区域内非直推；3、间推
     * @param innerRegion true、在区域内；false、不在
     * @param vipType
     * @return
     */
    public double serialCommission(int amount, int pushType, boolean innerRegion, int vipType) {
        return getRate(pushType, innerRegion, vipType) * amount;
    }

    public double serialCommission(int amount, double rate) {
        return amount * rate;
    }

    public double getRate(int pushType, boolean innerRegion, int vipType) {
        if (pushType == 1) {
            if (vipType == 3) {
                return RateSCEnum.DIRECT_LEVEL3.getRate();
            } else if (vipType == 2) {
                return RateSCEnum.DIRECT_LEVEL2.getRate();
            } else if (vipType == 1) {
                return RateSCEnum.DIRECT_LEVEL1.getRate();
            } else if (vipType == 21) {
                if (innerRegion) {
                    return RateSCEnum.DIRECT_PROVINCE_IN.getRate();
                } else {
                    return RateSCEnum.DIRECT_OUT.getRate();
                }
            } else if (vipType == 23) {
                if (innerRegion) {
                    return RateSCEnum.DIRECT_REGION_IN.getRate();
                } else {
                    return RateSCEnum.DIRECT_OUT.getRate();
                }
            } else {
                return 0;
            }
        } else if (pushType == 2) {
            if (vipType == 21 || vipType == 23) {
                return RateSCEnum.REGION_PROTECTED.getRate();
            } else {
                return 0;
            }
        } else if (pushType == 3) {
            if (vipType == 1 || vipType == 21 || vipType == 23) {
                return RateSCEnum.INDIRECT.getRate();
            }
        }
        return 0;
    }

    public DataTableBean merchantForList(ParamListBean param, UserInfo user) {
        DataTableBean data = new DataTableBean();
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        String countSql = "select count(*) from yqf_merchant ";
        String listSql = "select t4.id as merchantId,t4.merchant_name,ifnull(t3.order_amount,0) as order_amount,ifnull(t3.valid_order_count,0) as valid_order_count," +
                "ifnull(t3.valid_order_amount,0) as valid_order_amount,ifnull(t3.refund_count,0) as refund_count,ifnull(t3.refund_amount,0) as refund_amount," +
                "ifnull(t3.commission,0) as commission,ifnull(t3.income,0) as income,ifnull(t3.discount_amount,0) as discount_amount," +
                "ifnull(t3.refund_proportion,0) as refund_proportion from yqf_merchant as t4 left join (" +
                "select t2.merchant_id,sum(t1.order_amount) as order_amount,sum(t1.valid_order_count) as valid_order_count,sum(t1.valid_order_amount) as valid_order_amount,sum(t1.refund_count) as refund_count," +
                "sum(refund_amount) as refund_amount,sum(commission) as commission,sum(income) as income,sum(discount_amount) as discount_amount,sum(refund_proportion) as refund_proportion from  yqf_store_stats_day as t1 left join yqf_store as t2 on t1.store_id=t2.id " +
                " where t1.rec_date>='" + t[0] + "' and t1.rec_date<='" + t[1] + "' group by t2.merchant_id " +
                ") as t3 on t4.id=t3.merchant_id";
        if (user.getIsAdmin() == 0) {//非超管
            countSql += " where uid=" + user.getId();
            listSql += " where t4.uid=" + user.getId();
        }

        listSql += " order by t4.merchant_name limit " + (param.getPageNo() - 1) * param.getPageLength() + "," + param.getPageLength();
        //System.out.println(listSql);
        int tot = jdbcTemplate.queryForObject(countSql, Integer.class);
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(listSql);
        List<YqfStoreStatsDay> list = new ArrayList<>();
        YqfStoreStatsDay bean = null;
        Dict dict = Dict.create();
        for (Map<String, Object> map : list1) {
            dict.putAll(map);
            bean = new YqfStoreStatsDay();
            bean.setMerchantId(dict.getStr("merchantId"));
            bean.setMerchantName(dict.getStr("merchant_name"));
            bean.setOrderAmount(dict.getInt("order_amount"));
            bean.setValidOrderCount(dict.getInt("valid_order_count"));
            bean.setValidOrderAmount(dict.getInt("valid_order_amount"));
            bean.setRefundCount(dict.getInt("refund_count"));
            bean.setRefundAmount(dict.getInt("refund_amount"));
            bean.setCommission(dict.getInt("commission"));
            bean.setIncome(dict.getInt("income"));
            bean.setDiscountAmount(dict.getInt("discount_amount"));
            bean.setRefundProportion(dict.getDouble("refund_proportion"));
            list.add(bean);
        }

        data.setsEcho(param.getsEcho());
        data.setiTotalDisplayRecords(tot);
        data.setiTotalRecords(tot);
        data.setAaData(list);
        return data;
    }

    /**
     * 直推商户流水列表
     *
     * @param param
     * @param user
     * @param innerRegion 是否本区域内
     * @return
     */
    public DataTableBean parDirectMerchantForList(ParamListBean param, UserInfo user, boolean innerRegion) {
        DataTableBean data = new DataTableBean();
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        String countSql = "select count(*) from yqf_merchant ";
        String listSql = "select t4.id as merchantId,t4.merchant_name,ifnull(t3.order_amount,0) as order_amount,ifnull(t3.valid_order_count,0) as valid_order_count," +
                "ifnull(t3.valid_order_amount,0) as valid_order_amount,ifnull(t3.refund_count,0) as refund_count,ifnull(t3.refund_amount,0) as refund_amount," +
                "ifnull(t3.commission,0) as commission,ifnull(t3.income,0) as income,ifnull(t3.discount_amount,0) as discount_amount," +
                "ifnull(t3.refund_proportion,0) as refund_proportion from yqf_merchant as t4 left join (" +
                "select t2.merchant_id,sum(t1.order_amount) as order_amount,sum(t1.valid_order_count) as valid_order_count,sum(t1.valid_order_amount) as valid_order_amount,sum(t1.refund_count) as refund_count," +
                "sum(refund_amount) as refund_amount,sum(commission) as commission,sum(income) as income,sum(discount_amount) as discount_amount,sum(refund_proportion) as refund_proportion from  yqf_store_stats_day as t1 left join yqf_store as t2 on t1.store_id=t2.id " +
                " where t1.rec_date>='" + t[0] + "' and t1.rec_date<='" + t[1] + "' group by t2.merchant_id " +
                ") as t3 on t4.id=t3.merchant_id";
        if (user.getIsAdmin() == 0) {//非超管
            countSql += " where uid=" + user.getId();
            listSql += " where t4.uid=" + user.getId();

            if (user.getVipType() == 21) {
                if (innerRegion) {
                    countSql += " and region_code like '" + user.getRegionCode().substring(0, 2) + "%'";
                    listSql += " and t4.region_code like '" + user.getRegionCode().substring(0, 2) + "%'";
                } else {
                    countSql += " and region_code not like '" + user.getRegionCode().substring(0, 2) + "%'";
                    listSql += " and t4.region_code not like '" + user.getRegionCode().substring(0, 2) + "%'";
                }
            } else if (user.getVipType() == 23) {
                if (innerRegion) {
                    countSql += " and region_code='" + user.getRegionCode() + "'";
                    listSql += " and t4.region_code='" + user.getRegionCode() + "'";
                } else {
                    countSql += " and region_code!='" + user.getRegionCode() + "'";
                    listSql += " and t4.region_code!='" + user.getRegionCode() + "'";
                }
            }
        }

        listSql += " order by t4.merchant_name limit " + (param.getPageNo() - 1) * param.getPageLength() + "," + param.getPageLength();
//        System.err.println(listSql);
        int tot = jdbcTemplate.queryForObject(countSql, Integer.class);
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(listSql);
        List<YqfStoreStatsDay> list = new ArrayList<>();
        YqfStoreStatsDay bean = null;
        Dict dict = Dict.create();
        for (Map<String, Object> map : list1) {
            dict.putAll(map);
            bean = new YqfStoreStatsDay();
            bean.setMerchantId(dict.getStr("merchantId"));
            bean.setMerchantName(dict.getStr("merchant_name"));
            bean.setOrderAmount(dict.getInt("order_amount"));
            bean.setValidOrderCount(dict.getInt("valid_order_count"));
            bean.setValidOrderAmount(dict.getInt("valid_order_amount"));
            bean.setRefundCount(dict.getInt("refund_count"));
            bean.setRefundAmount(dict.getInt("refund_amount"));
            bean.setCommission(dict.getInt("commission"));
            bean.setIncome(dict.getInt("income"));
            bean.setDiscountAmount(dict.getInt("discount_amount"));
            bean.setRefundProportion(dict.getDouble("refund_proportion"));
            bean.setSerialCommission(serialCommission(dict.getInt("income") - dict.getInt("refund_amount"), 1, innerRegion, user.getVipType()));
            list.add(bean);
        }

        data.setsEcho(param.getsEcho());
        data.setiTotalDisplayRecords(tot);
        data.setiTotalRecords(tot);
        data.setAaData(list);
        return data;
    }

    /**
     * 本区域内非直推商户流水列表
     *
     * @param param
     * @param user
     * @return
     */
    public DataTableBean parRegionMerchantForList(ParamListBean param, UserInfo user) {
        DataTableBean data = new DataTableBean();
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        //yqf_merchant as t4 from ,yqf_store_stats_day as t1,yqf_store as t2
        String countSql = "select count(*) from yqf_merchant";
        String listSql = "select t4.id as merchantId,t4.merchant_name,ifnull(t3.order_amount,0) as order_amount,ifnull(t3.valid_order_count,0) as valid_order_count," +
                "ifnull(t3.valid_order_amount,0) as valid_order_amount,ifnull(t3.refund_count,0) as refund_count,ifnull(t3.refund_amount,0) as refund_amount," +
                "ifnull(t3.commission,0) as commission,ifnull(t3.income,0) as income,ifnull(t3.discount_amount,0) as discount_amount," +
                "ifnull(t3.refund_proportion,0) as refund_proportion from yqf_merchant as t4 left join (" +
                "select t2.merchant_id,sum(t1.order_amount) as order_amount,sum(t1.valid_order_count) as valid_order_count,sum(t1.valid_order_amount) as valid_order_amount,sum(t1.refund_count) as refund_count," +
                "sum(refund_amount) as refund_amount,sum(commission) as commission,sum(income) as income,sum(discount_amount) as discount_amount,sum(refund_proportion) as refund_proportion from  yqf_store_stats_day as t1 left join yqf_store as t2 on t1.store_id=t2.id " +
                " where t1.rec_date>='" + t[0] + "' and t1.rec_date<='" + t[1] + "' group by t2.merchant_id " +
                ") as t3 on t4.id=t3.merchant_id";
        if (user.getVipType() == 21) {
            countSql += " where region_code like '" + user.getRegionCode().substring(0, 2) + "%'";
            listSql += " where t4.region_code like '" + user.getRegionCode().substring(0, 2) + "%'";
        } else {
            countSql += " where region_code='" + user.getRegionCode() + "'";
            listSql += " where t4.region_code='" + user.getRegionCode() + "'";
        }
        countSql += " and uid!=" + user.getId();
        listSql += " and t4.uid!=" + user.getId();
        listSql += " order by t4.merchant_name limit " + (param.getPageNo() - 1) * param.getPageLength() + "," + param.getPageLength();
//        System.err.println("本区域内非直推商户流水列表sql："+listSql);
        int tot = jdbcTemplate.queryForObject(countSql, Integer.class);
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(listSql);
        List<YqfStoreStatsDay> list = new ArrayList<>();
        YqfStoreStatsDay bean = null;
        Dict dict = Dict.create();
        for (Map<String, Object> map : list1) {
            dict.putAll(map);
            bean = new YqfStoreStatsDay();
            bean.setMerchantId(dict.getStr("merchantId"));
            bean.setMerchantName(dict.getStr("merchant_name"));
            bean.setOrderAmount(dict.getInt("order_amount"));
            bean.setValidOrderCount(dict.getInt("valid_order_count"));
            bean.setValidOrderAmount(dict.getInt("valid_order_amount"));
            bean.setRefundCount(dict.getInt("refund_count"));
            bean.setRefundAmount(dict.getInt("refund_amount"));
            bean.setCommission(dict.getInt("commission"));
            bean.setIncome(dict.getInt("income"));
            bean.setDiscountAmount(dict.getInt("discount_amount"));
            bean.setRefundProportion(dict.getDouble("refund_proportion"));
            bean.setSerialCommission(serialCommission(dict.getInt("income") - dict.getInt("refund_amount"), 2, false, user.getVipType()));
            list.add(bean);
        }

        data.setsEcho(param.getsEcho());
        data.setiTotalDisplayRecords(tot);
        data.setiTotalRecords(tot);
        data.setAaData(list);
        return data;
    }

    /**
     * 间推商户流水
     *
     * @param param
     * @param user
     * @return
     */
    public DataTableBean parIndirectMerchantForList(ParamListBean param, UserInfo user) {
        DataTableBean data = new DataTableBean();
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        //yqf_merchant as t4 from ,yqf_store_stats_day as t1,yqf_store as t2
        String countSql = "select count(*) from yqf_merchant";
        String listSql = "select t4.id as merchantId,t4.merchant_name,ifnull(t3.order_amount,0) as order_amount,ifnull(t3.valid_order_count,0) as valid_order_count," +
                "ifnull(t3.valid_order_amount,0) as valid_order_amount,ifnull(t3.refund_count,0) as refund_count,ifnull(t3.refund_amount,0) as refund_amount," +
                "ifnull(t3.commission,0) as commission,ifnull(t3.income,0) as income,ifnull(t3.discount_amount,0) as discount_amount," +
                "ifnull(t3.refund_proportion,0) as refund_proportion from yqf_merchant as t4 left join (" +
                "select t2.merchant_id,sum(t1.order_amount) as order_amount,sum(t1.valid_order_count) as valid_order_count,sum(t1.valid_order_amount) as valid_order_amount,sum(t1.refund_count) as refund_count," +
                "sum(refund_amount) as refund_amount,sum(commission) as commission,sum(income) as income,sum(discount_amount) as discount_amount,sum(refund_proportion) as refund_proportion from  yqf_store_stats_day as t1 left join yqf_store as t2 on t1.store_id=t2.id " +
                " where t1.rec_date>='" + t[0] + "' and t1.rec_date<='" + t[1] + "' group by t2.merchant_id " +
                ") as t3 on t4.id=t3.merchant_id";

        countSql += " where uid in(select id from izf.user_info ui where ui.push_uid=" + user.getId() + " and ui.vip_type in(1,2,3))";
        listSql += " where t4.uid in(select id from izf.user_info ui where ui.push_uid=" + user.getId() + " and ui.vip_type in(1,2,3))";
        listSql += " order by t4.merchant_name limit " + (param.getPageNo() - 1) * param.getPageLength() + "," + param.getPageLength();
//        System.out.println("间推商户流水列表sql："+listSql);
        int tot = jdbcTemplate.queryForObject(countSql, Integer.class);
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(listSql);
        List<YqfStoreStatsDay> list = new ArrayList<>();
        YqfStoreStatsDay bean = null;
        Dict dict = Dict.create();
        for (Map<String, Object> map : list1) {
            dict.putAll(map);
            bean = new YqfStoreStatsDay();
            bean.setMerchantId(dict.getStr("merchantId"));
            bean.setMerchantName(dict.getStr("merchant_name"));
            bean.setOrderAmount(dict.getInt("order_amount"));
            bean.setValidOrderCount(dict.getInt("valid_order_count"));
            bean.setValidOrderAmount(dict.getInt("valid_order_amount"));
            bean.setRefundCount(dict.getInt("refund_count"));
            bean.setRefundAmount(dict.getInt("refund_amount"));
            bean.setCommission(dict.getInt("commission"));
            bean.setIncome(dict.getInt("income"));
            bean.setDiscountAmount(dict.getInt("discount_amount"));
            bean.setRefundProportion(dict.getDouble("refund_proportion"));
            bean.setSerialCommission(serialCommission(dict.getInt("income") - dict.getInt("refund_amount"), 3, false, user.getVipType()));
            list.add(bean);
        }

        data.setsEcho(param.getsEcho());
        data.setiTotalDisplayRecords(tot);
        data.setiTotalRecords(tot);
        data.setAaData(list);
        return data;
    }

    public DataTableBean storeForList(ParamListBean param, UserInfo user) {
        DataTableBean data = new DataTableBean();
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        String merchantId = param.getParam().get("merchantId");
        String countSql = "select count(*) from yqf_store where 1=1 ";
        String listSql = "select t1.id,t1.store_no,t1.store_name,t1.merchant_id,t1.merchant_name,ifnull(t2.order_amount,0) as order_amount,ifnull(t2.valid_order_count,0) as valid_order_count," +
                "ifnull(t2.valid_order_amount,0) as valid_order_amount,ifnull(t2.refund_count,0) as refund_count,ifnull(t2.refund_amount,0) as refund_amount," +
                "ifnull(t2.commission,0) as commission,ifnull(t2.income,0) as income,ifnull(t2.discount_amount,0) as discount_amount," +
                "ifnull(t2.refund_proportion,0) as refund_proportion from yqf_store as t1 left join(" +
                "select store_id,sum(order_amount) as order_amount,sum(valid_order_count) as valid_order_count,sum(valid_order_amount) as valid_order_amount," +
                "sum(refund_count) as refund_count,sum(refund_amount) as refund_amount,sum(commission) as commission,sum(income) as income,sum(discount_amount) as discount_amount," +
                "sum(refund_proportion) as refund_proportion from  yqf_store_stats_day where rec_date>='" + t[0] + "' and rec_date<='" + t[1] + "' group by store_id) as t2 on t1.id =t2.store_id" +
                " where 1=1 ";
        if (!StringUtils.isEmpty(merchantId)) {
            countSql += " and merchant_id='" + merchantId + "'";
            listSql += " and t1.merchant_id='" + merchantId + "'";
        }
        listSql += " order by t1.merchant_id,t1.id limit " + (param.getPageNo() - 1) * param.getPageLength() + "," + param.getPageLength();
        //System.out.println(listSql);
        int tot = jdbcTemplate.queryForObject(countSql, Integer.class);
        List<Map<String, Object>> list1 = jdbcTemplate.queryForList(listSql);
        List<YqfStoreStatsDay> list = new ArrayList<>();
        YqfStoreStatsDay bean = null;
        YqfStore store = null;
        Dict dict = Dict.create();
        for (Map<String, Object> map : list1) {
            dict.putAll(map);
            bean = new YqfStoreStatsDay();
            store = new YqfStore();
            store.setId(dict.getStr("id"));
            store.setStoreNo(dict.getInt("store_no"));
            store.setStoreName(dict.getStr("store_name"));
            store.setMerchantName(dict.getStr("merchant_name"));
            bean.setMerchantId(dict.getStr("merchantId"));
            bean.setMerchantName(dict.getStr("merchant_name"));
            bean.setOrderAmount(dict.getInt("order_amount"));
            bean.setValidOrderCount(dict.getInt("valid_order_count"));
            bean.setValidOrderAmount(dict.getInt("valid_order_amount"));
            bean.setRefundCount(dict.getInt("refund_count"));
            bean.setRefundAmount(dict.getInt("refund_amount"));
            bean.setCommission(dict.getInt("commission"));
            bean.setIncome(dict.getInt("income"));
            bean.setDiscountAmount(dict.getInt("discount_amount"));
            bean.setRefundProportion(dict.getDouble("refund_proportion"));
            bean.setYqfStore(store);
            list.add(bean);
        }

        data.setsEcho(param.getsEcho());
        data.setiTotalDisplayRecords(tot);
        data.setiTotalRecords(tot);
        data.setAaData(list);
        return data;
    }

    public DataTableBean storeDaylist(ParamListBean param, UserInfo user) {
        String datetimeTime = param.getParam().get("datetime_time");
        String[] t = datetimeTime.split("至");
        PageRequest pageRequest = buildPageRequestSort(param.getPageNo(), param.getPageLength(), "id", param.getFieldName(), param.getCompositorType());
        Page<YqfStoreStatsDay> page = yqfStoreSatsDayDao.findAll(new Specification<YqfStoreStatsDay>() {
            @Override
            public Predicate toPredicate(Root<YqfStoreStatsDay> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = Lists.newArrayList();
                predicates.add(cb.conjunction());
                predicates.add(cb.equal(root.get("storeId").as(String.class), param.getParam().get("storeId")));
                predicates.add(cb.greaterThanOrEqualTo(root.get("recDate"), DateUtil.parse(t[0])));
                predicates.add(cb.lessThanOrEqualTo(root.get("recDate"), DateUtil.parse(t[1])));
                return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }
        }, pageRequest);
//		Page<UserInfo> page = userInfoDao.findAll(pageRequest);
        return dataTableBean(page, param);
    }
}
